﻿using System;
using System.Collections.Generic;
using System.Text;
using ColdSun.Commons;
using ColdSun.Commons.Entities;
using Microsoft.Office.Interop.Excel;
using ColdSun.Commons.Configs;
using System.IO;
using System.Reflection;
using ColdSun.Framework;
using System.Globalization;
using Microsoft.Office.Interop.Word;

namespace ColdSun.Services
{
    public class InsuranceService
    {
        public string GenerateInsuranceByBranchForWeek(
            string originalFile, ColdSun.Commons.Entities.Replacement replacementInfo, AccountService accountService,
            Insurance selectInsurance, DateTime reportDate, DateTime reportFromDate,
            DateTime reportToDate, ref string exportFile, string exportFolder)
        {
            Worksheet originalWorksheet = null;
            Worksheet workingWorksheet = null;
            Worksheet settlementWorksheet = null;
            Workbook workingWorkbook = null;
            Workbook originalWorkbook = null;
            Microsoft.Office.Interop.Excel.Application excelApplication = null;
            try
            {
                if (!File.Exists(originalFile))
                {
                    Logger.Info(string.Format("Can't find the source file {0}", originalFile));
                    return string.Format("Can't find the source file {0}", originalFile);
                }
                Logger.Info(string.Format("The original file is {0}", originalFile));
                
                excelApplication = new Microsoft.Office.Interop.Excel.Application();
                originalWorkbook = excelApplication.Workbooks.Open(originalFile, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                originalWorksheet = (Worksheet)originalWorkbook.Worksheets.get_Item(1);
                Logger.Info(string.Format("Process original sheet {0}", originalWorksheet.Name));

                string exportTemplateFile = Utilities.GetFullFileName(CommonConfigs.InsuranceFireTemplateFile);
                if (!IsInsuranceFirer(selectInsurance.Type))
                {
                    exportTemplateFile = Utilities.GetFullFileName(CommonConfigs.InsuranceIncidentTemplateFile);
                }
                workingWorkbook = excelApplication.Workbooks.Open(exportTemplateFile);
                workingWorksheet = (Worksheet)workingWorkbook.Worksheets.get_Item(1);
                Logger.Info(string.Format("Process working sheet {0}", workingWorksheet.Name));
                settlementWorksheet = (Worksheet)workingWorkbook.Worksheets.get_Item(2);
                Logger.Info(string.Format("Process settlements sheet {0}", settlementWorksheet.Name));

                exportFile = string.Format("{0}\\{1}", exportFolder, exportFile);
                exportFile = exportFile.Replace("#InsuranceName#", selectInsurance.Name);
                exportFile = exportFile.Replace("#Date#", reportDate.ToString(CommonConfigs.InsuranceDateTimeFormatInFileName));
                exportFile = exportFile.Replace("#FromDate#", reportFromDate.ToString(CommonConfigs.InsuranceDateTimeFormatInFileName));
                exportFile = exportFile.Replace("#ToDate#", reportToDate.ToString(CommonConfigs.InsuranceDateTimeFormatInFileName));
                
                workingWorkbook.SaveAs(exportFile);
                Logger.Info(string.Format("Save as to file {0}", exportFile));

                int originalRowIndex = CommonConfigs.InsuranceOriginalHeaderRowIndex + 1;
                int exportRowIndex = CommonConfigs.InsuranceTemplateHeaderRowIndex + 1;
                bool isFirstOfWorking = true;
                
                while (true)
                {
                    object departmentValue_N = GetValue(originalWorksheet, "N", originalRowIndex);
                    if (string.IsNullOrEmpty(departmentValue_N.ToString()))
                    {
                        originalRowIndex++;
                        break;
                    }
                    Department currentDepartment = GetDepartmentByInsurance(selectInsurance.Type, departmentValue_N.ToString());
                    if (currentDepartment == null)
                    {
                        //Logger.Warn(string.Format("Please check the Branch ID {0}", departmentValue_N));
                        if (departmentValue_N.ToString().Length != 2 && departmentValue_N.ToString().Length != 3)
                        {
                            Logger.Warn(string.Format("Please check the Branch ID column (M) in the original file"));
                        }
                        originalRowIndex++;
                        continue;
                    }

                    if (!isFirstOfWorking) // Check duplicate Account
                    {
                        object originalIDNumber_E = GetValue(originalWorksheet, "E", originalRowIndex);
                        object workingIDNumber_G = GetValue(workingWorksheet, "G", exportRowIndex);
                        if (originalIDNumber_E.Equals(workingIDNumber_G))
                        {
                            originalRowIndex++;
                            continue;
                        }
                    }

                    bool isCheck = true;
                    if (IsInsuranceFirer(selectInsurance.Type))
                    {
                        object conditionValue_Q = GetValue(originalWorksheet, "Q", originalRowIndex);
                        object conditionValue_R = GetValue(originalWorksheet, "R", originalRowIndex);
                        if (!IsInMarket(conditionValue_Q) &&  !IsInMarket(conditionValue_R))
                        {
                            isCheck = false;
                        }
                    }

                    if (isCheck)
                    {
                        Account currentAccount = accountService.GetAccount(string.Format("{0}", GetValue(originalWorksheet, replacementInfo.ContractIDColumn, originalRowIndex)));
                        
                        if (!isFirstOfWorking)
                        {
                            exportRowIndex++;
                            Microsoft.Office.Interop.Excel.Range row = (Microsoft.Office.Interop.Excel.Range)workingWorksheet.Rows[exportRowIndex];
                            row.Insert(XlInsertShiftDirection.xlShiftDown);
                        }

                        isFirstOfWorking = false;
                        foreach (CopyColumnDetail detail in replacementInfo.CopyColumnDetails)
                        {
                            if (detail.CopyType.Equals("None", StringComparison.OrdinalIgnoreCase))
                            {
                            }
                            else if (detail.CopyType.Equals("Index", StringComparison.OrdinalIgnoreCase))
                            {
                                PasteValue(workingWorksheet, detail.DestColumn, exportRowIndex, string.Format("{0}", exportRowIndex - CommonConfigs.InsuranceTemplateHeaderRowIndex));
                            }
                            else
                            {
                                object copyValue = GetValue(originalWorksheet, detail.SourceColumn, originalRowIndex);
                                Microsoft.Office.Interop.Excel.Range currentDestCell = originalWorksheet.get_Range(Utilities.Cell(detail.DestColumn, exportRowIndex));
                                currentDestCell.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlDash;
                                currentDestCell.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
                                currentDestCell.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
                                currentDestCell.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlDash;
                                if (detail.CopyType.Equals("String", StringComparison.OrdinalIgnoreCase))
                                {
                                    PasteValue(workingWorksheet, detail.DestColumn, exportRowIndex, string.Format("'{0}", copyValue));
                                }
                                else if (detail.CopyType.Equals("Contract", StringComparison.OrdinalIgnoreCase))
                                {
                                    try
                                    {
                                        if (currentAccount != null)
                                        {
                                            copyValue = currentAccount.ContractID;
                                        }
                                        else
                                        {
                                            copyValue = GetValue(originalWorksheet, "C", originalRowIndex);
                                        }
                                        PasteValue(workingWorksheet, detail.DestColumn, exportRowIndex, string.Format("'{0}", copyValue));
                                    }
                                    catch { }
                                }
                                else if (detail.CopyType.Equals("ThoiHanVay", StringComparison.OrdinalIgnoreCase))
                                {
                                    try
                                    {
                                        if (currentAccount != null)
                                        {
                                            copyValue = currentAccount.ThoiHanVay;
                                            PasteValue(workingWorksheet, detail.DestColumn, exportRowIndex, string.Format("{0}", copyValue));
                                        }
                                    }
                                    catch { }
                                }
                                else if (detail.CopyType.Equals("TaiSan", StringComparison.OrdinalIgnoreCase))
                                {
                                    try
                                    {
                                        if (currentAccount != null)
                                        {
                                            copyValue = currentAccount.TaiSan;
                                            PasteValue(workingWorksheet, detail.DestColumn, exportRowIndex, string.Format("'{0}", copyValue));
                                        }
                                    }
                                    catch { }
                                }
                                else if (detail.CopyType.Equals("Department", StringComparison.OrdinalIgnoreCase))
                                {
                                    try
                                    {
                                        string contractID = "";
                                        try
                                        {
                                            if (currentAccount != null)
                                            {
                                                contractID = currentAccount.ContractID;
                                            }
                                            else
                                            {
                                                contractID = GetValue(originalWorksheet, "C", originalRowIndex).ToString();
                                            }
                                        }
                                        catch { }

                                        Department departmentTemplate = DepartmentService.GetInstance().GetDepartment(string.Format("{0}", Convert.ToInt32(copyValue)), contractID);
                                        if (departmentTemplate != null)
                                        {
                                            copyValue = departmentTemplate.DepartmentName;
                                        }
                                    }
                                    catch { }
                                    PasteValue(workingWorksheet, detail.DestColumn, exportRowIndex, string.Format("'{0}", copyValue));
                                }
                                else if (detail.CopyType.Equals("DateTime", StringComparison.OrdinalIgnoreCase))
                                {
                                    PasteValue(workingWorksheet, detail.DestColumn, exportRowIndex, copyValue);
                                    try
                                    {
                                        DateTime copyDateValue = Convert.ToDateTime(copyValue);
                                        if (copyDateValue.Day == 1 && copyDateValue.Month == 1)
                                        {
                                            workingWorksheet.get_Range(Utilities.Cell(detail.DestColumn, exportRowIndex)).NumberFormat = "yyyy";
                                        }
                                        else
                                        {
                                            workingWorksheet.get_Range(Utilities.Cell(detail.DestColumn, exportRowIndex)).NumberFormat = "dd/MM/yyyy";
                                        }
                                    }catch{}

                                }
                                else if (detail.CopyType.Equals("ReportDate", StringComparison.OrdinalIgnoreCase))
                                {
                                    PasteValue(workingWorksheet, detail.DestColumn, exportRowIndex, reportDate);
                                }
                                else if (detail.CopyType.Equals("SettlementDate", StringComparison.OrdinalIgnoreCase))
                                {
                                }
                                else if (detail.CopyType.Equals("FixValue", StringComparison.OrdinalIgnoreCase))
                                {
                                    PasteValue(workingWorksheet, detail.DestColumn, exportRowIndex, detail.CopyFormat);
                                }
                                else if (detail.CopyType.Equals("Formula", StringComparison.OrdinalIgnoreCase))
                                {
                                    PasteValue(workingWorksheet, detail.DestColumn, exportRowIndex, string.Format(detail.CopyFormat, exportRowIndex));
                                }
                                else
                                {
                                    PasteValue(workingWorksheet, detail.DestColumn, exportRowIndex, copyValue);
                                }
                            }
                        }
                    }
                    
                    originalRowIndex++;
                }

                string reportDateRange = string.Format("{0}", GetValue(workingWorksheet, "A", 3));
                reportDateRange = reportDateRange.Replace("#FromDate#", reportFromDate.ToString("dd/MM/yyyy"));
                reportDateRange = reportDateRange.Replace("#ToDate#", reportToDate.ToString("dd/MM/yyyy"));
                PasteValue(workingWorksheet, "A", 3, reportDateRange);

                string totalValue = string.Format("{0}", GetValue(workingWorksheet, "A", 4));
                PasteValue(workingWorksheet, "A", 4, string.Format("{0}", totalValue.Replace("#Total#", (exportRowIndex - CommonConfigs.InsuranceTemplateHeaderRowIndex).ToString())));

                PasteValue(workingWorksheet, "I", exportRowIndex + 1, string.Format("=SUM(I{0}:I{1})", CommonConfigs.InsuranceTemplateHeaderRowIndex + 1, exportRowIndex));
                PasteValue(workingWorksheet, "N", exportRowIndex + 1, string.Format("=SUM(N{0}:N{1})", CommonConfigs.InsuranceTemplateHeaderRowIndex + 1, exportRowIndex));
                PasteValue(workingWorksheet, "D", exportRowIndex + 4, CommonConfigs.InsuranceCompanyName(selectInsurance.Type));
                
                workingWorkbook.Save();
                return "Successful";
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                return ex.Message + ex.StackTrace;
            }
            finally
            {
                ReleaseComObjectService.ReleaseComObject(originalWorksheet);
                ReleaseComObjectService.ReleaseComObject(workingWorksheet);

                ReleaseComObjectService.ReleaseWorkbookObject(originalWorkbook, false);
                ReleaseComObjectService.ReleaseWorkbookObject(workingWorkbook);

                ReleaseComObjectService.ReleaseExcelApplicationObject(excelApplication);

                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

        public string GenerateSettlementSheet(string settlementFile, string followFile, AccountService accountService,
            Insurance selectInsurance)
        {
            if (accountService == null || accountService.GetSettlementAccount == null || accountService.GetSettlementAccount.Count == 0)
            {
                Logger.Info("Don't have settlement date record");
                return "Susscessful";
            }
            Worksheet followWorksheet = null;
            Worksheet settlementWorksheet = null;
            Workbook settlementWorkbook = null;
            Workbook followWorkbook = null;
            Microsoft.Office.Interop.Excel.Application excelApplication = null;
            try
            {
                if (!File.Exists(settlementFile))
                {
                    Logger.Info(string.Format("Can't find the settlement file {0}", settlementFile));
                    return string.Format("Can't find the settlement file {0}", settlementFile);
                }
                Logger.Info(string.Format("The original file is {0}", settlementFile));

                if (!File.Exists(followFile))
                {
                    Logger.Info(string.Format("Can't find the follow file {0}", followFile));
                    return string.Format("Can't find the follow file {0}", followFile);
                }

                excelApplication = new Microsoft.Office.Interop.Excel.Application();

                followWorkbook = excelApplication.Workbooks.Open(followFile, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                followWorksheet = (Worksheet)followWorkbook.Worksheets.get_Item(1);
                Logger.Info(string.Format("Process original sheet {0}", followWorksheet.Name));


                settlementWorkbook = excelApplication.Workbooks.Open(settlementFile);
                settlementWorksheet = (Worksheet)settlementWorkbook.Worksheets["Tat toan"];
                Logger.Info(string.Format("Process working sheet {0}", settlementWorksheet.Name));

                int settlementHeaderRowIndex = 1;
                int settlementRowIndex = 2;
                bool isFirstOfSettlement = true;

                foreach (Account account in accountService.GetSettlementAccount)
                {
                    object departmentValue = GetValue(followWorksheet, "C", account.SettlementIndex);
                    if (string.IsNullOrEmpty(departmentValue.ToString()))
                    {
                        continue;
                    }

                    Department currentDepartment = GetDepartmentByInsurance(selectInsurance.Type, departmentValue.ToString());
                    if (currentDepartment == null)
                    {
                        //Logger.Warn(string.Format("Please check the Branch ID {0}", departmentValue_N));
                        if (departmentValue.ToString().Length != 2 && departmentValue.ToString().Length != 3)
                        {
                            Logger.Warn(string.Format("Please check the Branch ID column (M) in the original file"));
                        }
                        continue;
                    }

                    if (!isFirstOfSettlement)
                    {
                        settlementRowIndex++;
                        ((Microsoft.Office.Interop.Excel.Range)settlementWorksheet.Rows[settlementRowIndex]).Insert(XlInsertShiftDirection.xlShiftDown);
                    }
                    isFirstOfSettlement = false;
                    PasteValue(settlementWorksheet, "A", settlementRowIndex, string.Format("{0}", settlementRowIndex - settlementHeaderRowIndex));
                    CopyValue(followWorksheet, "B", account.SettlementIndex, settlementWorksheet, "B", settlementRowIndex); // Chi nhánh
                    CopyValue(followWorksheet, "J", account.SettlementIndex, settlementWorksheet, "C", settlementRowIndex); //Ngày tháng
                    CopyValue(followWorksheet, "F", account.SettlementIndex, settlementWorksheet, "D", settlementRowIndex); //Họ và tên 
                    CopyValue(followWorksheet, "G", account.SettlementIndex, settlementWorksheet, "E", settlementRowIndex); //Số HĐ tín dụng vay vốn
                    PasteValue(settlementWorksheet, "F", settlementRowIndex, string.Format("{0}", "")); //Năm sinh
                    PasteValue(settlementWorksheet, "G", settlementRowIndex, string.Format("{0}", account.CIF)); //Số CMND
                    PasteValue(settlementWorksheet, "H", settlementRowIndex, string.Format("{0}", "")); //Địa chỉ
                    CopyValue(followWorksheet, "H", account.SettlementIndex, settlementWorksheet, "I", settlementRowIndex); //Số tiền bảo hiểm (bằng số tiền vay ban đầu)
                    CopyValue(followWorksheet, "J", account.SettlementIndex, settlementWorksheet, "J", settlementRowIndex); //Ngày hiệu lực thời hạn bảo hiểm
                    CopyValue(followWorksheet, "K", account.SettlementIndex, settlementWorksheet, "K", settlementRowIndex); //Ngày kết thúc thời hạn bảo hiểm
                    CopyValue(followWorksheet, "I", account.SettlementIndex, settlementWorksheet, "L", settlementRowIndex); //Thời hạn bảo hiểm
                    PasteValue(settlementWorksheet, "M", settlementRowIndex, string.Format("{0}", "0.0001")); //Tỷ lệ phí bảo hiểm
                    PasteValue(settlementWorksheet, "N", settlementRowIndex, string.Format("=I{0}*L{0}*M{0}", settlementRowIndex)); //Phí bảo hiểm
                    if (selectInsurance.Type == 0 || selectInsurance.Type == 1)
                    {
                        CopyValue(followWorksheet, "M", account.SettlementIndex, settlementWorksheet, "O", settlementRowIndex); //Chợ
                        CopyValue(followWorksheet, "I", account.SettlementIndex, settlementWorksheet, "P", settlementRowIndex); //Thời hạn vay
                        CopyValue(followWorksheet, "J", account.SettlementIndex, settlementWorksheet, "Q", settlementRowIndex); //NGÀY GỬI
                        PasteValue(settlementWorksheet, "R", settlementRowIndex, account.SettlementDate.Value.ToString("dd/MM/yyyy")); //Tất toán
                    }
                    else
                    {
                        CopyValue(followWorksheet, "I", account.SettlementIndex, settlementWorksheet, "O", settlementRowIndex); //Thời hạn vay
                        CopyValue(followWorksheet, "J", account.SettlementIndex, settlementWorksheet, "P", settlementRowIndex); //NGÀY GỬI
                        PasteValue(settlementWorksheet, "Q", settlementRowIndex, account.SettlementDate.Value.ToString("dd/MM/yyyy")); //Tất toán
                    }
                }
                settlementWorkbook.Save();
                return "Successful";
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                return ex.Message + ex.StackTrace;
            }
            finally
            {
                ReleaseComObjectService.ReleaseComObject(followWorksheet);
                ReleaseComObjectService.ReleaseComObject(settlementWorksheet);

                ReleaseComObjectService.ReleaseWorkbookObject(followWorkbook, false);
                ReleaseComObjectService.ReleaseWorkbookObject(settlementWorkbook);

                ReleaseComObjectService.ReleaseExcelApplicationObject(excelApplication);

                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

        public string GenerateInsuranceByBranchForMonthToDoc(Insurance selectInsurance, Workbook insuranceBCTHWorkbook, DateTime fromDate, DateTime toDate, int insuranceForMonthBCTHIndex, int insuranceForMonthSettlementIndex, string exportFolder)
        {
            Microsoft.Office.Interop.Word.Application templatePL06Obj = null;            
            Document templatePL06Doc = null;

            Microsoft.Office.Interop.Word.Application templateBM02Obj = null;
            Document templateBM02Doc = null;
            //Document templateBM02Doc = null;
            try
            {
                string pl06Template = GetPL06TemplateFile(selectInsurance.Type);
                if (!File.Exists(pl06Template))
                {
                    Logger.Info(string.Format("Can't find the template file (PL06) for month {0}", pl06Template));
                    return string.Format("Can't find the template file (PL06) for month {0}", pl06Template);
                }

                string bm02Template = GetBM02TemplateFile(selectInsurance.Type);
                if (!File.Exists(bm02Template))
                {
                    Logger.Info(string.Format("Can't find the template file (BM02) for month {0}", bm02Template));
                    return string.Format("Can't find the template file (BM02) for month {0}", bm02Template);
                }

                object pl06TemplateFile = Utilities.GetFullFileName(pl06Template);
                templatePL06Obj = new Microsoft.Office.Interop.Word.Application();
                templatePL06Obj.Visible = false;

                object missing = Missing.Value;
                //Fill PL06
                templatePL06Doc = new Document();
                templatePL06Doc = templatePL06Obj.Documents.Open(ref pl06TemplateFile);

                object replaceAll = Microsoft.Office.Interop.Word.WdReplace.wdReplaceAll;
                Microsoft.Office.Interop.Word.Find findObject = templatePL06Obj.Selection.Find;

                findObject.Text = "f_dd";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Replacement.Text = fromDate.ToString("dd");
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);

                findObject.Text = "f_MM";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Replacement.Text = fromDate.ToString("MM");
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);

                findObject.Text = "f_yyyy";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Replacement.Text = fromDate.ToString("yyyy");
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);

                findObject.Text = "t_dd";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Replacement.Text = toDate.ToString("dd");
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);

                findObject.Text = "t_MM";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Replacement.Text = toDate.ToString("MM");
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);

                findObject.Text = "t_yyyy";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Replacement.Text = toDate.ToString("yyyy");
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);

                Worksheet replaceWorksheet = (Worksheet)insuranceBCTHWorkbook.Sheets.get_Item(1);
                replaceWorksheet.Range[string.Format("A1:G{0}", insuranceForMonthBCTHIndex)].Copy();
                findObject.Text = "#TableNumberOfContracts#";
                findObject.Wrap = WdFindWrap.wdFindContinue;  
                findObject.Execute();
                findObject.Replacement.Application.Selection.Paste();
                findObject.Replacement.Text = ""; ;
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);

                replaceWorksheet = (Worksheet)insuranceBCTHWorkbook.Sheets.get_Item(2);
                replaceWorksheet.Range[string.Format("A1:G{0}", insuranceForMonthSettlementIndex)].Copy();
                findObject.Text = "#TableNumberOfSettlementContracts#";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Execute();
                findObject.Replacement.Application.Selection.Paste();
                findObject.Replacement.Text = ""; ;
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);

                replaceWorksheet = (Worksheet)insuranceBCTHWorkbook.Sheets.get_Item(3);
                replaceWorksheet.Range[string.Format("A1:E2")].Copy();
                findObject.Text = "#TableTotalInsuranceFee#";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Execute();
                findObject.Replacement.Application.Selection.Paste();
                findObject.Replacement.Text = ""; ;
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);

                object finalFee = GetValue(replaceWorksheet, "E", 2);                
                findObject.Text = "#TotalFeeByCharacters#";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Replacement.Text = Utilities.ChangeMoneyToText(finalFee);
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);
                
                //save for BCTH for weeks
                FileInfo fileInfo = new FileInfo(pl06Template);
                string saveFile = string.Format("{0}\\{1}", exportFolder, fileInfo.Name);
                templatePL06Doc.SaveAs(saveFile);
                Logger.Info(string.Format("Save as to file {0}", saveFile));

                //// Fill BM02
                object bm02TemplateFile = Utilities.GetFullFileName(bm02Template);
                templateBM02Obj = new Microsoft.Office.Interop.Word.Application();
                templateBM02Obj.Visible = false;
                templateBM02Doc = new Document();
                templateBM02Doc = templateBM02Obj.Documents.Open(ref bm02TemplateFile);
                findObject = templateBM02Obj.Selection.Find;

                double finalFeeValue = Convert.ToDouble(finalFee.ToString());
                findObject.Text = "#TotalFeeByNumbers#";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Replacement.Text = finalFeeValue.ToString("##,#");
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);
                
                findObject.Text = "#TotalFeeByCharacters#";
                findObject.Wrap = WdFindWrap.wdFindContinue;
                findObject.Replacement.Text = Utilities.ChangeMoneyToText(finalFee);
                findObject.Execute(ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref missing, ref missing, ref missing, ref missing, ref missing,
                    ref replaceAll, ref missing, ref missing, ref missing, ref missing);

                //save for BCTH for Settlement
                fileInfo = new FileInfo(bm02Template);
                saveFile = string.Format("{0}\\{1}", exportFolder, fileInfo.Name);
                templateBM02Doc.SaveAs(saveFile);
                Logger.Info(string.Format("Save as to BCTH for Settlement file {0}", saveFile));
                
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                return ex.Message;
            }
            finally
            {
                if (templatePL06Doc != null)
                {
                    templatePL06Doc.Close();
                }

                if (templatePL06Obj != null)
                {
                    templatePL06Obj.Quit();
                    ReleaseComObjectService.ReleaseComObject(templatePL06Obj);
                }

                if (templateBM02Doc != null)
                {
                    templateBM02Doc.Close();
                }

                if (templateBM02Obj != null)
                {
                    templateBM02Obj.Quit();
                    ReleaseComObjectService.ReleaseComObject(templateBM02Obj);
                }

                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            return "";
        }

        public string GenerateInsuranceByBranchForMonth(string originalFile, string weekSheetName, string branchSheetName, Insurance selectInsurance, string exportFile, string exportFolder)
        {
            Worksheet originalWorksheet = null;
            Worksheet settlementWorksheet = null;
            Workbook originalWorkbook = null;
            Workbook insuranceForMonthWorkbook = null;
            Worksheet insuranceForMonthWorksheet = null;
            Workbook insuranceForMonthBCTHWorkbook = null;
            Worksheet insuranceForMonthBCTHWorksheet = null;
            Worksheet insuranceForMonthSettlementWorksheet = null;
            Microsoft.Office.Interop.Excel.Application excelApplication = null;
            try
            {
                if (!File.Exists(originalFile))
                {
                    Logger.Info(string.Format("Can't find the source file {0}", originalFile));
                    return string.Format("Can't find the source file {0}", originalFile);
                }
                Logger.Info(string.Format("The original file is {0}", originalFile));

                if (string.IsNullOrEmpty(weekSheetName))
                {
                    Logger.Info(string.Format("WeekSheetName is null or empty"));
                    return string.Format("WeekSheetName is null or empty");
                }

                if (string.IsNullOrEmpty(branchSheetName))
                {
                    Logger.Info(string.Format("BranchSheetName is null or empty"));
                    return string.Format("BranchSheetName is null or empty");
                }

                string insuranceForMonthTemplate = CommonConfigs.InsuranceForBranchByMonthBKCPOfIncidentTemplateFile;
                if (selectInsurance.Type > 1)
                {
                    insuranceForMonthTemplate = CommonConfigs.InsuranceForBranchByMonthBKCPOfFireTemplateFile;
                }
                if (!File.Exists(insuranceForMonthTemplate))
                {
                    Logger.Info(string.Format("Can't find the template file for month {0}", insuranceForMonthTemplate));
                    return string.Format("Can't find the template file for month {0}", insuranceForMonthTemplate);
                }

                string insuranceForMonthBCTHTemplate = CommonConfigs.InsuranceForBranchByMonthBCTHTemplateFile;
                if (!File.Exists(insuranceForMonthBCTHTemplate))
                {
                    Logger.Info(string.Format("Can't find the BCTH template file for month {0}", insuranceForMonthBCTHTemplate));
                    return string.Format("Can't find the BCTH template file for month {0}", insuranceForMonthBCTHTemplate);
                }

                excelApplication = new Microsoft.Office.Interop.Excel.Application();

                DateTime fromDate = new DateTime();
                DateTime toDate = new DateTime();
                List<int> insuranceForMonthBCTHEndIndexs = new List<int>();

                // get original info
                originalWorkbook = excelApplication.Workbooks.Open(originalFile, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                originalWorksheet = (Worksheet)originalWorkbook.Worksheets[weekSheetName];// .get_Item(1);
                Logger.Info(string.Format("Process original sheet {0}", originalWorksheet.Name));
                originalWorksheet.Copy(Type.Missing, originalWorksheet);
                originalWorksheet = (Worksheet)originalWorkbook.Worksheets[string.Format("{0} (2)", weekSheetName)];
                originalWorksheet.Name = branchSheetName;
                settlementWorksheet = (Worksheet)originalWorkbook.Worksheets["TAT TOAN"];
                Logger.Info(string.Format("Process settlement sheet {0}", settlementWorksheet.Name));

                // get template file for branch by month
                insuranceForMonthWorkbook = excelApplication.Workbooks.Open(insuranceForMonthTemplate, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                insuranceForMonthWorksheet = (Worksheet)insuranceForMonthWorkbook.Worksheets.get_Item(1);

                // get template file for branch by month BCTH
                insuranceForMonthBCTHWorkbook = excelApplication.Workbooks.Open(insuranceForMonthBCTHTemplate, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                insuranceForMonthBCTHWorksheet = (Worksheet)insuranceForMonthBCTHWorkbook.Worksheets.get_Item(1);

                int insuranceForMonthIndex = CommonConfigs.InsuranceForBranchByMonthHeaderRowIndex + 1;
                int insuranceForMonthBCTHIndex = CommonConfigs.InsuranceForBranchByMonthBCTHHeaderRowIndex + 1;
                int summaryIndex = 1;

                int originalRowIndex = CommonConfigs.InsuranceOriginalHeaderRowIndex + 1;
                int exportRowIndex = CommonConfigs.InsuranceTemplateHeaderRowIndex + 1;
                
                //Find the header index of original sheet
                int headerRowIndex = FindHeaderRowIndex(originalWorksheet);

                int detailRowIndex = headerRowIndex + 1;
                bool isTotalRowByWeek = false;
                int totalRecordsInWeek = 0;
                bool isFirstWeek = true;
                while (detailRowIndex < 3000) // find and delete Total row by week
                {
                    object currentRowValue = GetValue(originalWorksheet, "A", detailRowIndex);
                    if (currentRowValue == null)
                    {
                        break;
                    }
                    int currentRowValueToInt;
                    if (int.TryParse(currentRowValue.ToString(), out currentRowValueToInt))
                    {
                        detailRowIndex++;
                        totalRecordsInWeek++;
                        isTotalRowByWeek = false;
                    }
                    else
                    {
                        if (isTotalRowByWeek)
                        {
                            //Get From and To date of week
                            
                            //DateTime fromDateOfWeek;
                            //delete row (BCTH)
                            insuranceForMonthBCTHIndex++;
                            Microsoft.Office.Interop.Excel.Range deleteBCTHRow = (Microsoft.Office.Interop.Excel.Range)insuranceForMonthBCTHWorksheet.Rows[insuranceForMonthBCTHIndex];
                            deleteBCTHRow.EntireRow.Delete(null);
                            //Paste summary value to BCTH
                            PasteValue(insuranceForMonthBCTHWorksheet, "D", insuranceForMonthBCTHIndex, string.Format("=SUM(D{0}:D{1})", CommonConfigs.InsuranceForBranchByMonthBCTHHeaderRowIndex + 1, insuranceForMonthBCTHIndex - 1));
                            PasteValue(insuranceForMonthBCTHWorksheet, "E", insuranceForMonthBCTHIndex, string.Format("=SUM(E{0}:E{1})", CommonConfigs.InsuranceForBranchByMonthBCTHHeaderRowIndex + 1, insuranceForMonthBCTHIndex - 1));
                            PasteValue(insuranceForMonthBCTHWorksheet, "F", insuranceForMonthBCTHIndex, string.Format("=SUM(F{0}:F{1})", CommonConfigs.InsuranceForBranchByMonthBCTHHeaderRowIndex + 1, insuranceForMonthBCTHIndex - 1));
                            //Paste summary value to BCTH - Tong Hop
                            object summaryForMonthBCTHValue = GetValue(insuranceForMonthBCTHWorksheet, "F", insuranceForMonthBCTHIndex);
                            insuranceForMonthBCTHWorksheet = (Worksheet)insuranceForMonthBCTHWorkbook.Worksheets.get_Item(3);
                            PasteValue(insuranceForMonthBCTHWorksheet, "B", 2, summaryForMonthBCTHValue);
                            break;
                        }
                        isTotalRowByWeek = true;
                        object totalFeeInWeek = GetValue(originalWorksheet, CommonConfigs.InsuranceForBranchByMonthTotalFeeColumn, detailRowIndex);
                        DateTime dateInWeek = Convert.ToDateTime(GetValue(originalWorksheet, "C", detailRowIndex - 1));
                        DateTime fromDateOfWeek = Utilities.GetFirstDayOfWeek(dateInWeek);
                        DateTime toDateOfWeek = Utilities.GetLastDayOfWeek(dateInWeek);
                        toDate = toDateOfWeek;
                        // Fill to BCTH
                        if (isFirstWeek)
                        {
                            fromDate = fromDateOfWeek;
                        }
                        else
                        {
                            insuranceForMonthBCTHIndex++;
                            Microsoft.Office.Interop.Excel.Range row = (Microsoft.Office.Interop.Excel.Range)insuranceForMonthBCTHWorksheet.Rows[insuranceForMonthBCTHIndex];
                            row.Insert(XlInsertShiftDirection.xlShiftDown);
                        }
                        isFirstWeek = false;
                        PasteValue(insuranceForMonthBCTHWorksheet, "A", insuranceForMonthBCTHIndex, insuranceForMonthBCTHIndex - CommonConfigs.InsuranceForBranchByMonthBCTHHeaderRowIndex);
                        PasteValue(insuranceForMonthBCTHWorksheet, "C", insuranceForMonthBCTHIndex, string.Format("  {0} - {1}", fromDateOfWeek.ToString("dd/MM/yyyy"), toDateOfWeek.ToString("dd/MM/yyyy")));
                        PasteValue(insuranceForMonthBCTHWorksheet, "D", insuranceForMonthBCTHIndex, totalRecordsInWeek);
                        PasteValue(insuranceForMonthBCTHWorksheet, "E", insuranceForMonthBCTHIndex, totalRecordsInWeek);
                        PasteValue(insuranceForMonthBCTHWorksheet, "F", insuranceForMonthBCTHIndex, totalFeeInWeek);
                        totalRecordsInWeek = 0;

                        //Delete Total row by week in original
                        originalWorksheet.get_Range(Utilities.Cell("A", detailRowIndex)).EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp);
                    }
                }
                //Sort by branch name
                Microsoft.Office.Interop.Excel.Range sortByBranch = originalWorksheet.get_Range(Utilities.Cell("A", headerRowIndex + 1), Utilities.Cell("P", detailRowIndex - 1));
                sortByBranch.Sort(
                sortByBranch.Columns["B", Missing.Value], Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending,
                Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending,
                Missing.Value, Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending,
                Microsoft.Office.Interop.Excel.XlYesNoGuess.xlNo, Missing.Value, Missing.Value,
                Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortColumns,
                Microsoft.Office.Interop.Excel.XlSortMethod.xlPinYin,
                Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal,
                Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal,
                Microsoft.Office.Interop.Excel.XlSortDataOption.xlSortNormal);
                
                //Insert total rows
                int totalRows = detailRowIndex - headerRowIndex;
                int totalBranchs = 0;
                int indexOfBranch = 1;
                string sumColumnNames = CommonConfigs.InsuranceForBranchByMonthSumColumns;
                string sumRowIndexs = "";
                for (int index = 1; index < totalRows; index++) // Find each branch and add total row
                {
                    int currentRowIndex = headerRowIndex + index + totalBranchs;
                    PasteValue(originalWorksheet, "A", currentRowIndex, indexOfBranch);
                    string currentBranch = GetValue(originalWorksheet, "B", currentRowIndex).ToString().Trim();
                    string nextBranch = GetValue(originalWorksheet, "B", currentRowIndex + 1).ToString().Trim();
                    if (currentBranch != "" && currentBranch.Equals(nextBranch, StringComparison.OrdinalIgnoreCase))
                    {
                        indexOfBranch++;
                        continue;
                    }
                    else if (currentBranch != "") // add total row
                    {
                        // add total row
                        currentRowIndex++;
                        Microsoft.Office.Interop.Excel.Range totalBranchRow = originalWorksheet.get_Range(Utilities.Cell("A", currentRowIndex));
                        totalBranchRow.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown);
                        Microsoft.Office.Interop.Excel.Range totalCells = originalWorksheet.get_Range(Utilities.Cell("A", currentRowIndex), Utilities.Cell("P", currentRowIndex));

                        totalCells.Cells.Interior.Color = System.Drawing.Color.FromName(CommonConfigs.InsuranceForBranchByMonthSumRowColor).ToArgb();
                        totalCells.EntireRow.Font.Bold = true;

                        Microsoft.Office.Interop.Excel.Range mergeTotalCells = originalWorksheet.get_Range(Utilities.Cell("A", currentRowIndex), Utilities.Cell("D", currentRowIndex));
                        mergeTotalCells.MergeCells = true;
                        mergeTotalCells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                        mergeTotalCells.Value = currentBranch;

                        object summaryValue = null; 

                        foreach (string sumColumnName in sumColumnNames.Split(','))
                        {
                            PasteValue(originalWorksheet, sumColumnName.Trim(), currentRowIndex, string.Format("=SUM({0}{1}:{0}{2})", sumColumnName, currentRowIndex - indexOfBranch, currentRowIndex - 1));
                            //if (summaryValue == null)
                            {
                                summaryValue = GetValue(originalWorksheet, sumColumnName, currentRowIndex);
                            }
                        }
                        sumRowIndexs += "," + currentRowIndex.ToString();
                        totalBranchs++;
                        indexOfBranch = 1;

                        // Add to insuranceForMonthWorksheet
                        PasteValue(insuranceForMonthWorksheet, "C", insuranceForMonthIndex, summaryValue);
                        Department currentDepartment = DepartmentService.GetInstance().GetDepartmentByName(currentBranch);
                        if (currentDepartment == null)
                        {
                            PasteValue(insuranceForMonthWorksheet, "A", insuranceForMonthIndex, currentBranch);
                        }
                        else
                        {
                            PasteValue(insuranceForMonthWorksheet, "A", insuranceForMonthIndex, currentDepartment.DepartmentFullName);
                            PasteValue(insuranceForMonthWorksheet, "B", insuranceForMonthIndex, string.Format("'{0}", currentDepartment.DepartmentFullCode));
                        }
                        summaryIndex++;
                        //Add copy template for insurance for Month
                        if (totalBranchs > 0)
                        {
                            insuranceForMonthIndex++;
                            Microsoft.Office.Interop.Excel.Range row = (Microsoft.Office.Interop.Excel.Range)insuranceForMonthWorksheet.Rows[insuranceForMonthIndex];
                            row.Insert(XlInsertShiftDirection.xlShiftDown);
                        }
                        //insuranceForMonthIndex++;
                    }
                }

                sumRowIndexs = sumRowIndexs.Remove(0, 1); //Remove last row which is not used.
                foreach (string sumColumnName in sumColumnNames.Split(',')) // Summary amount for all branchs
                {
                    string totalValue = "";
                    foreach (string sumRowIndex in sumRowIndexs.Split(','))
                    {
                        totalValue += string.Format(",{0}{1}", sumColumnName, sumRowIndex);
                    }
                    totalValue = string.Format("=SUM({0})", totalValue.Remove(0,1));
                    PasteValue(originalWorksheet, sumColumnName.Trim(), detailRowIndex + totalBranchs, totalValue);
                }
                //PasteValue(originalWorksheet, "C", 4, totalRows -1);

                //delete row (Month)
                Microsoft.Office.Interop.Excel.Range deleteRow = (Microsoft.Office.Interop.Excel.Range)insuranceForMonthWorksheet.Rows[insuranceForMonthIndex];
                deleteRow.EntireRow.Delete(null);
                //Paste summary value to BKCP theo CN,TTTCCD
                PasteValue(insuranceForMonthWorksheet, "C", insuranceForMonthIndex, string.Format("=SUM(C{0}:C{1})", CommonConfigs.InsuranceForBranchByMonthHeaderRowIndex + 1, insuranceForMonthIndex -1));
                object summaryForMonthValue = GetValue(insuranceForMonthWorksheet, "C", insuranceForMonthIndex);

                //Paste summary value to BKCP Summary
                insuranceForMonthWorksheet = (Worksheet)insuranceForMonthWorkbook.Worksheets.get_Item(2);
                PasteValue(insuranceForMonthWorksheet, "C", CommonConfigs.InsuranceForBranchByMonthBKCPNHCDIndex, summaryForMonthValue);

                //Copy BKCP theo CN,TTTCCD to original
                insuranceForMonthWorksheet.Copy(Type.Missing, originalWorksheet);
                insuranceForMonthWorksheet = (Worksheet)insuranceForMonthWorkbook.Worksheets.get_Item(1);
                insuranceForMonthWorksheet.Copy(Type.Missing, originalWorksheet);

                //Paste TAT TOAN value to Huy Hop - BCTH
                insuranceForMonthSettlementWorksheet = (Worksheet)insuranceForMonthBCTHWorkbook.Worksheets.get_Item(2);
                bool isFirstRecord = true;
                headerRowIndex = FindHeaderRowIndex(settlementWorksheet);
                detailRowIndex = headerRowIndex + 1;
                int insuranceForMonthSettlementIndex = 2;
                while (detailRowIndex < 3000) // Copy settlement from TAT TOAN (original) to BCTH
                {
                    object currentRowValue = GetValue(settlementWorksheet, "A", detailRowIndex);
                    if (currentRowValue == null)
                    {
                        break;
                    }
                    int currentRowValueToInt;
                    if (int.TryParse(currentRowValue.ToString(), out currentRowValueToInt))
                    {
                        if (isFirstRecord)
                        {
                            isFirstRecord = false;
                            PasteValue(insuranceForMonthSettlementWorksheet, "A", insuranceForMonthSettlementIndex, insuranceForMonthSettlementIndex - 1);
                            CopyValue(settlementWorksheet, "B", detailRowIndex, insuranceForMonthSettlementWorksheet, "B", insuranceForMonthSettlementIndex);
                            CopyValue(settlementWorksheet, "C", detailRowIndex, insuranceForMonthSettlementWorksheet, "C", insuranceForMonthSettlementIndex);
                            PasteValue(insuranceForMonthSettlementWorksheet, "D", insuranceForMonthSettlementIndex, 1);
                            PasteValue(insuranceForMonthSettlementWorksheet, "E", insuranceForMonthSettlementIndex, 1);
                            CopyValue(settlementWorksheet, "P", detailRowIndex, insuranceForMonthSettlementWorksheet, "F", insuranceForMonthSettlementIndex);
                        }
                        else 
                        {
                            object previousDate = GetValue(settlementWorksheet, "C", detailRowIndex - 1);
                            object currentDate = GetValue(settlementWorksheet, "C", detailRowIndex);
                            object previousBranch = GetValue(settlementWorksheet, "B", detailRowIndex - 1);
                            object currentBranch = GetValue(settlementWorksheet, "B", detailRowIndex);

                            if (previousDate.Equals(currentDate) && previousBranch.Equals(currentBranch))
                            {
                                object currentBCTHValue = GetValue(insuranceForMonthSettlementWorksheet, "D", insuranceForMonthSettlementIndex);
                                PasteValue(insuranceForMonthSettlementWorksheet, "D", insuranceForMonthSettlementIndex, Convert.ToInt32(currentBCTHValue) + 1);
                                currentBCTHValue = GetValue(insuranceForMonthSettlementWorksheet, "E", insuranceForMonthSettlementIndex);
                                PasteValue(insuranceForMonthSettlementWorksheet, "E", insuranceForMonthSettlementIndex, Convert.ToInt32(currentBCTHValue) + 1);
                                currentBCTHValue = GetValue(insuranceForMonthSettlementWorksheet, "F", insuranceForMonthSettlementIndex);
                                object currentValue = GetValue(settlementWorksheet, "P", detailRowIndex);
                                PasteValue(insuranceForMonthSettlementWorksheet, "F", insuranceForMonthSettlementIndex, Convert.ToDouble(currentBCTHValue) + Convert.ToDouble(currentValue));
                            }
                            else
                            {
                                insuranceForMonthSettlementIndex++;
                                Microsoft.Office.Interop.Excel.Range row = (Microsoft.Office.Interop.Excel.Range)insuranceForMonthSettlementWorksheet.Rows[insuranceForMonthSettlementIndex];
                                row.Insert(XlInsertShiftDirection.xlShiftDown);

                                PasteValue(insuranceForMonthSettlementWorksheet, "A", insuranceForMonthSettlementIndex, insuranceForMonthSettlementIndex - 1);
                                CopyValue(settlementWorksheet, "B", detailRowIndex, insuranceForMonthSettlementWorksheet, "B", insuranceForMonthSettlementIndex);
                                CopyValue(settlementWorksheet, "C", detailRowIndex, insuranceForMonthSettlementWorksheet, "C", insuranceForMonthSettlementIndex);
                                PasteValue(insuranceForMonthSettlementWorksheet, "D", insuranceForMonthSettlementIndex, 1);
                                PasteValue(insuranceForMonthSettlementWorksheet, "E", insuranceForMonthSettlementIndex, 1);
                                CopyValue(settlementWorksheet, "P", detailRowIndex, insuranceForMonthSettlementWorksheet, "F", insuranceForMonthSettlementIndex);
                            }
                        }
                        
                        detailRowIndex++;
                    }
                    else
                    {
                        insuranceForMonthSettlementIndex++;
                        Microsoft.Office.Interop.Excel.Range settlementRow = (Microsoft.Office.Interop.Excel.Range)insuranceForMonthSettlementWorksheet.Rows[insuranceForMonthSettlementIndex];
                        settlementRow.EntireRow.Delete(null);
                        //Paste summary value to settlement sheet
                        PasteValue(insuranceForMonthSettlementWorksheet, "D", insuranceForMonthSettlementIndex, string.Format("=SUM(D{0}:D{1})", 2, insuranceForMonthSettlementIndex - 1));
                        PasteValue(insuranceForMonthSettlementWorksheet, "E", insuranceForMonthSettlementIndex, string.Format("=SUM(E{0}:E{1})", 2, insuranceForMonthSettlementIndex - 1));
                        PasteValue(insuranceForMonthSettlementWorksheet, "F", insuranceForMonthSettlementIndex, string.Format("=SUM(F{0}:F{1})", 2, insuranceForMonthSettlementIndex - 1));
                        object summaryForMonthSettlementValue = GetValue(insuranceForMonthSettlementWorksheet, "F", insuranceForMonthSettlementIndex);
                        PasteValue(insuranceForMonthBCTHWorksheet, "C", 2, summaryForMonthSettlementValue);
                        break;
                    }
                }

                //Doc file
                GenerateInsuranceByBranchForMonthToDoc(selectInsurance, insuranceForMonthBCTHWorkbook, fromDate, toDate, insuranceForMonthBCTHIndex, insuranceForMonthSettlementIndex, exportFolder);

                //Save for Branch (Original)
                string saveFile = string.Format("{0}\\{1}", exportFolder, exportFile);
                saveFile = saveFile.Replace("#Date#", fromDate.ToString("MM yyyy"));
                originalWorkbook.SaveAs(saveFile);
                Logger.Info(string.Format("Save as to file {0}", saveFile));

                //save for Branch (Summary)
                FileInfo fileInfo = new FileInfo(insuranceForMonthTemplate);
                saveFile = string.Format("{0}\\{1}", exportFolder, fileInfo.Name);
                saveFile = saveFile.Replace("#Date#", fromDate.ToString("MM yyyy"));
                insuranceForMonthWorkbook.SaveAs(saveFile);

                //save for Branch (Summary)
                FileInfo fileBCTHInfo = new FileInfo(CommonConfigs.InsuranceForBranchByMonthBCTHTemplateFile);
                saveFile = string.Format("{0}\\{1}", exportFolder, fileBCTHInfo.Name);
                saveFile = saveFile.Replace("#Date#", fromDate.ToString("MM yyyy"));
                insuranceForMonthBCTHWorkbook.SaveAs(saveFile);
                return "Successful";
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                return ex.Message + ex.StackTrace;
            }
            finally
            {
                ReleaseComObjectService.ReleaseComObject(originalWorksheet);
                ReleaseComObjectService.ReleaseComObject(settlementWorksheet);
                ReleaseComObjectService.ReleaseWorkbookObject(originalWorkbook);

                ReleaseComObjectService.ReleaseComObject(insuranceForMonthWorksheet);
                ReleaseComObjectService.ReleaseWorkbookObject(insuranceForMonthWorkbook);

                ReleaseComObjectService.ReleaseComObject(insuranceForMonthBCTHWorksheet);
                ReleaseComObjectService.ReleaseComObject(insuranceForMonthSettlementWorksheet);
                ReleaseComObjectService.ReleaseWorkbookObject(insuranceForMonthBCTHWorkbook);

                ReleaseComObjectService.ReleaseExcelApplicationObject(excelApplication);

                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

        private int FindHeaderRowIndex(Worksheet findWorksheet)
        {
            int headerRowIndex = 1;
            while (headerRowIndex < 1000)
            {
                object findHeaderRow = GetValue(findWorksheet, "A", headerRowIndex);
                if (findHeaderRow != null && findHeaderRow.ToString().Equals("STT", StringComparison.OrdinalIgnoreCase))
                {
                    break;
                }
                headerRowIndex++;
            }
            return headerRowIndex;
        }

        public string GenerateInsurance_BKCPForMonth(string originalFile1, string originalFile2, string sheetName, Insurance selectInsurance, string exportFolder)
        {
            Workbook originalWorkbook1 = null;
            Worksheet originalWorksheet1 = null;

            Workbook originalWorkbook2 = null;
            Worksheet originalWorksheet2 = null;
            
            Workbook insuranceForMonthBKCPWorkbook = null;
            Worksheet insuranceForMonthBKCPWorksheet = null;
            Worksheet insuranceForMonthBKTHWorksheet = null;
            Microsoft.Office.Interop.Excel.Application excelApplication = null;
            try
            {
                if (!File.Exists(originalFile1))
                {
                    Logger.Info(string.Format("Can't find the source file 1 {0}", originalFile1));
                    return string.Format("Can't find the source file 1 {0}", originalFile1);
                }
                Logger.Info(string.Format("The original file 1 is {0}", originalFile1));

                if (!File.Exists(originalFile2))
                {
                    Logger.Info(string.Format("Can't find the source file 2 {0}", originalFile2));
                    return string.Format("Can't find the source file 2 {0}", originalFile2);
                }
                Logger.Info(string.Format("The original file 2 is {0}", originalFile2));

                if (string.IsNullOrEmpty(sheetName))
                {
                    Logger.Info(string.Format("SheetName is null or empty"));
                    return string.Format("SheetName is null or empty");
                }

                string insuranceBKCPForMonthTemplate = CommonConfigs.InsuranceForBranchByMonthBKCPOfIncidentTemplateFile;
                if (selectInsurance.Type == 1)
                {
                    insuranceBKCPForMonthTemplate = CommonConfigs.InsuranceForBranchByMonthBKCPOfFireTemplateFile;
                }
                if (!File.Exists(insuranceBKCPForMonthTemplate))
                {
                    Logger.Info(string.Format("Can't find the BKCP template file for month {0}", insuranceBKCPForMonthTemplate));
                    return string.Format("Can't find the BKCP template file for month {0}", insuranceBKCPForMonthTemplate);
                }

                excelApplication = new Microsoft.Office.Interop.Excel.Application();

                // get original file 1
                originalWorkbook1 = excelApplication.Workbooks.Open(originalFile1, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                originalWorksheet1 = (Worksheet)originalWorkbook1.Worksheets[sheetName];
                originalWorksheet1.Name = originalWorksheet1.Name + " (1)";
                Logger.Info(string.Format("Process original file 1 sheet {0}", originalWorksheet1.Name));
                
                // get original file 1
                originalWorkbook2 = excelApplication.Workbooks.Open(originalFile2, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                originalWorksheet2 = (Worksheet)originalWorkbook2.Worksheets[sheetName];
                originalWorksheet2.Name = originalWorksheet2.Name + " (2)";
                Logger.Info(string.Format("Process original file 2 sheet {0}", originalWorksheet2.Name));

                // get template file for branch by month BKCP
                insuranceForMonthBKCPWorkbook = excelApplication.Workbooks.Open(insuranceBKCPForMonthTemplate, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                insuranceForMonthBKCPWorksheet = (Worksheet)insuranceForMonthBKCPWorkbook.Worksheets.get_Item(1);
                insuranceForMonthBKTHWorksheet = (Worksheet)insuranceForMonthBKCPWorkbook.Worksheets.get_Item(2);

                originalWorksheet2.Copy(Type.Missing, insuranceForMonthBKCPWorksheet);
                originalWorksheet1.Copy(Type.Missing, insuranceForMonthBKCPWorksheet);

                int insuranceForMonthBKCPIndex = CommonConfigs.InsuranceForBranchByMonthHeaderRowIndex + 1;
                bool isFirstRecord = true;
                CopyBKCPToSummarySheet(originalWorksheet1, insuranceForMonthBKCPWorksheet, ref insuranceForMonthBKCPIndex, ref isFirstRecord);
                CopyBKCPToSummarySheet(originalWorksheet2, insuranceForMonthBKCPWorksheet, ref insuranceForMonthBKCPIndex, ref isFirstRecord);

                //Calculate the sum of money
                //In the detail sheet
                insuranceForMonthBKCPIndex++;
                PasteValue(insuranceForMonthBKCPWorksheet, "C", insuranceForMonthBKCPIndex, string.Format("=SUM(C{0}:C{1})", CommonConfigs.InsuranceForBranchByMonthHeaderRowIndex + 1, insuranceForMonthBKCPIndex - 1));
                object summaryForMonthValue = GetValue(insuranceForMonthBKCPWorksheet, "C", insuranceForMonthBKCPIndex);
                //in Summary sheet
                PasteValue(insuranceForMonthBKTHWorksheet, "C", CommonConfigs.InsuranceForBranchByMonthBKCPNHCDIndex, summaryForMonthValue);

                //save for Branch (Summary)
                FileInfo fileBKCPInfo = new FileInfo(insuranceBKCPForMonthTemplate);
                string saveFile = string.Format("{0}\\{1}", exportFolder, fileBKCPInfo.Name.Replace(fileBKCPInfo.Extension, " " + DateTime.Now.ToString("MMyyyy") + fileBKCPInfo.Extension));
                insuranceForMonthBKCPWorkbook.SaveAs(saveFile);
                return "Successful";
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                return ex.Message + ex.StackTrace;
            }
            finally
            {
                ReleaseComObjectService.ReleaseComObject(originalWorksheet1);                
                ReleaseComObjectService.ReleaseWorkbookObject(originalWorkbook1, false);

                ReleaseComObjectService.ReleaseComObject(originalWorksheet2);                
                ReleaseComObjectService.ReleaseWorkbookObject(originalWorkbook2, false);

                ReleaseComObjectService.ReleaseComObject(insuranceForMonthBKCPWorksheet);
                ReleaseComObjectService.ReleaseWorkbookObject(insuranceForMonthBKCPWorkbook);

                ReleaseComObjectService.ReleaseExcelApplicationObject(excelApplication);

                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

        private void CopyBKCPToSummarySheet(Worksheet originalWorksheet, Worksheet insuranceForMonthBKCPWorksheet, ref int insuranceForMonthBKCPIndex, ref bool isFirstRecord)
        {
            int insuranceForMonthOriginalIndex = CommonConfigs.InsuranceForBranchByMonthHeaderRowIndex + 1;
            while (true)
            {
                if (!isFirstRecord)
                {
                    insuranceForMonthBKCPIndex++;
                    Microsoft.Office.Interop.Excel.Range row = (Microsoft.Office.Interop.Excel.Range)insuranceForMonthBKCPWorksheet.Rows[insuranceForMonthBKCPIndex];
                    row.Insert(XlInsertShiftDirection.xlShiftDown);
                }
                isFirstRecord = false;
                CopyValue(originalWorksheet, "A", insuranceForMonthOriginalIndex, insuranceForMonthBKCPWorksheet, "A", insuranceForMonthBKCPIndex);
                object currentBranchId = GetValue(originalWorksheet, "B", insuranceForMonthOriginalIndex);
                PasteValue(insuranceForMonthBKCPWorksheet, "B", insuranceForMonthBKCPIndex, string.Format("'{0}", currentBranchId));
                CopyValue(originalWorksheet, "C", insuranceForMonthOriginalIndex, insuranceForMonthBKCPWorksheet, "C", insuranceForMonthBKCPIndex);
                object nextRecord = GetValue(originalWorksheet, "A", insuranceForMonthOriginalIndex + 2); //Abort total record
                insuranceForMonthOriginalIndex++;
                if (nextRecord == null || nextRecord.ToString().Trim() == string.Empty)
                {
                    break;
                }
            }
        }

        #region Utilities
        private void CopyValue(Worksheet sourceWorksheet, string sourceColumnName, int sourceRowIndex, Worksheet destWorksheet, string destColumnName, int destRowIndex)
        {
            destWorksheet.get_Range(Utilities.Cell(destColumnName, destRowIndex)).Value = sourceWorksheet.get_Range(Utilities.Cell(sourceColumnName, sourceRowIndex)).Value;
        }

        private object GetValue(Worksheet sourceWorksheet, string sourceColumnName, int sourceRowIndex)
        {
            return string.Format("{0}", sourceWorksheet.get_Range(Utilities.Cell(sourceColumnName, sourceRowIndex)).Value);
        }

        private void PasteValue(Worksheet destWorksheet, string destColumnName, int destRowIndex, object value)
        {
            PasteValue(destWorksheet, destColumnName, destRowIndex, true, value);
        }

        private void PasteValue(Worksheet destWorksheet, string destColumnName, int destRowIndex, bool isPasteValue, object value)
        {
            if (isPasteValue)
            {
                destWorksheet.get_Range(Utilities.Cell(destColumnName, destRowIndex)).Value = value;
            }
        }

        private bool IsInMarket(object sourceValue)
        {
            if (sourceValue == null)
            {
                return false;
            }
            string sourceValueString = sourceValue.ToString();
            if (string.IsNullOrEmpty(sourceValueString))
            {
                return false;
            }
            string[] constanValue = "SAP ,KIOT ,CHO ,SAP -,KIOT -,CHO -,SAP-,KIOT-,CHO-,TTTM".Split(',');
            string[] notConstanValue = "BDS,QSDD,QSD DAT".Split(',');
            foreach (string item in notConstanValue)
            {
                if (sourceValueString.ToUpper().Contains(item.ToUpper()))
                {
                    return false;
                }
            }

            foreach (string item in constanValue)
            {
                if (sourceValueString.ToUpper().Contains(item.ToUpper()))
                {
                    return true;
                }
            }
            return false;
        }

        public Department GetDepartmentByInsurance(int selectType, string departmentCode)
        {
            switch (selectType)
            {
                case 0:
                    return DepartmentService.GetInstance().GetDepartmentInsuranceToanCau(departmentCode);
                case 1:
                    return DepartmentService.GetInstance().GetDepartmentInsuranceAAA(departmentCode);
                case 2:
                    return DepartmentService.GetInstance().GetDepartmentInsuranceBaoMinh(departmentCode);
                default:
                    return DepartmentService.GetInstance().GetDepartmentInsurancePVI(departmentCode);
            }
        }

        public bool IsInsuranceFirer(int selectType)
        {
            if (selectType <= 1) return true;
            return false;
        }

        public string GetPL06TemplateFile(int selectType)
        {
            switch (selectType)
            {
                case 0:
                    return CommonConfigs.InsuranceForBranchByMonthPL06OfToanCauTemplateFile;
                case 1:
                    return CommonConfigs.InsuranceForBranchByMonthPL06OfAAATemplateFile;
                case 2:
                    return CommonConfigs.InsuranceForBranchByMonthPL06OfBaoMinhTemplateFile;
                default:
                    return CommonConfigs.InsuranceForBranchByMonthPL06OfPVITemplateFile;
            }
        }

        public string GetBM02TemplateFile(int selectType)
        {
            switch (selectType)
            {
                case 0:
                    return CommonConfigs.InsuranceForBranchByMonthBM02OfToanCauTemplateFile;
                case 1:
                    return CommonConfigs.InsuranceForBranchByMonthBM02OfAAATemplateFile;
                case 2:
                    return CommonConfigs.InsuranceForBranchByMonthBM02OfBaoMinhTemplateFile;
                default:
                    return CommonConfigs.InsuranceForBranchByMonthBM02OfPVITemplateFile;
            }
        }
        #endregion
    }
}
